"""
Copyright (c) 2022 Huawei Technologies Co.,Ltd.

openGauss is licensed under Mulan PSL v2.
You can use this software according to the terms and conditions of the Mulan PSL v2.
You may obtain a copy of Mulan PSL v2 at:

          http://license.coscl.org.cn/MulanPSL2

THIS SOFTWARE IS PROVIDED ON AN "AS IS" BASIS, WITHOUT WARRANTIES OF ANY KIND,
EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO NON-INFRINGEMENT,
MERCHANTABILITY OR FIT FOR A PARTICULAR PURPOSE.
See the Mulan PSL v2 for more details.
"""
"""
Case Type   : 二级分区
Case Name   : 二级分区表拼音排序测试
Create At   : 2022/2/26
Owner       : opentestcase012
Description :
    1.创建库，指定编码UTF8,创建表空间
    2.创建range_range二级分区表
    3.插入数据
    4.查询数据nlssort拼音排序
    5.不同语句格式查询数据nlssort拼音排序
    6.查询nlssort后的数据
    7.查询数据nlssort拼音排序
    8.查询数据不同nlssort拼音排序
    9.创建list_list二级分区表
    10.插入数据并查询分区数据nlssort拼音排序
    11.创建hash_hash二级分区表
    12.插入数据并查询分区数据nlssort拼音排序
    13.查询分区数据nlssort拼音排序
    14.清理环境
Expect      :
    1.成功
    2.成功
    3.成功
    4.成功
    5.成功，等价上一条语句
    6.成功
    7.成功
    8.成功,不等价上一条语句
    9.成功
    10.成功
    11.成功
    12.成功
    13.成功,第一列相同的时候,col_2的排序与上一条相反
    14.清理成功
History     :
"""
import os
import unittest

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import Node
from yat.test import macro


class Subpartition(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.primaryNode = Node('PrimaryDbUser')
        self.constant = Constant()
        self.t_name = 't_subpartition_0354'
        self.ts_name = 'ts_subpartition_0354'
        self.db_name = 'db_subpartition_0354'
        self.copy_file = os.path.join(macro.DB_INSTANCE_PATH,
                                      'f_subpartition_0354.dat')

    def test_subpartition(self):
        text = '-----step1:创建库，指定编码UTF8,创建表空间 expect:成功-----'
        self.log.info(text)
        create_sql = f"drop database if exists {self.db_name};" \
            f"create database {self.db_name} encoding 'UTF8' " \
            f"template = template0;" \
            f"drop table if exists {self.t_name};" \
            f"drop tablespace if exists {self.ts_name};" \
            f"create tablespace {self.ts_name} relative location " \
            f"'{self.ts_name}';"
        msg = self.pri_sh.execut_db_sql(create_sql)
        self.log.info(msg)
        self.assertIn(self.constant.DROP_DATABASE_SUCCESS, msg,
                      '执行失败:' + text)
        self.assertIn(self.constant.CREATE_DATABASE_SUCCESS, msg,
                      '执行失败:' + text)
        self.assertIn(self.constant.TABLESPCE_CREATE_SUCCESS, msg,
                      '执行失败:' + text)

        text = '-----step2:创建range_range二级分区表 expect:成功-----'
        self.log.info(text)
        create_sql = f"drop table if exists {self.t_name};" \
            f"create table {self.t_name} (col_1 char(40), col_2 char(40) , " \
            f"col_3 varchar2 ( 30 ) , col_4 int )tablespace {self.ts_name} " \
            f"partition by range (col_1) subpartition by range (col_2) " \
            f"(partition p_range_1 values less than('a') " \
            f"(subpartition p_range_1_1 values less than( 5 )," \
            f"subpartition p_range_1_2 values less than( 50 )," \
            f"subpartition p_range_1_3 values less than( maxvalue ))," \
            f"partition p_range_2 values less than( 'b' )" \
            f"(subpartition p_range_2_1 values less than( 0 )," \
            f"subpartition p_range_2_2 values less than( 10 )," \
            f"subpartition p_range_2_3 values less than( 20 )," \
            f"subpartition p_range_2_4 values less than( 30 )," \
            f"subpartition p_range_2_5 values less than( maxvalue ))," \
            f"partition p_range_3 values less than( 'c')" \
            f"(subpartition p_range_3_5 values less than( maxvalue ))," \
            f"partition p_range_4 values less than( 'd')," \
            f"partition p_range_5 values less than('gong')," \
            f"partition p_range_6 values less than( maxvalue ));"
        msg = self.pri_sh.execut_db_sql(create_sql, dbname=self.db_name)
        self.log.info(msg)
        self.assertIn(self.constant.DROP_TABLE_SUCCESS, msg, '执行失败:' + text)
        self.assertIn(self.constant.CREATE_TABLE_SUCCESS, msg, '执行失败:' + text)

        text = '-----step3:插入数据 expect:成功-----'
        self.log.info(text)
        sql = f"insert into {self.t_name} values('工商银行','icbc')," \
            f"('中国银行','BOC'),('建设银行','CCB'),('设银','是否')," \
            f"('银行','稳定');" \
            f"insert into {self.t_name} values('招商银行','还有')," \
            f"('农业银行','稳定'),('知识银行','剂'),('图书馆','被删除'),('图书馆','删除');"
        msg = self.pri_sh.execut_db_sql(sql, dbname=self.db_name)
        self.log.info(msg)
        self.assertEqual(2, msg.count(self.constant.INSERT_SUCCESS_MSG),
                         '执行失败:' + text)

        text = '-----step4:查询数据nlssort拼音排序 expect:成功-----'
        self.log.info(text)
        sql = f"select * from {self.t_name} order by " \
            f"nlssort(col_1,'nls_sort = schinese_pinyin_m') asc,col_2;"
        select_res1 = self.pri_sh.execut_db_sql(sql, dbname=self.db_name)
        self.log.info(select_res1)
        select_exp1 = ['工商银行', '建设银行', '农业银行', '设银', '图书馆',
                       '图书馆', '银行', '招商银行', '知识银行', '中国银行']
        for i in range(10):
            self.assertIn(select_exp1[i], select_res1.splitlines()[-(11 - i)],
                          '执行失败:' + text)

        text = '-----step5:不同语句格式查询数据nlssort拼音排序 expect:成功，等价上一条语句-----'
        self.log.info(text)
        sql = f"select * from (select * from {self.t_name} order by nlssort" \
            f"(col_1,'nls_sort = schinese_pinyin_m') desc,col_2 desc) " \
            f"order by nlssort(col_1,'nls_sort = schinese_pinyin_m') " \
            f"asc,col_2 asc;"
        select_res2 = self.pri_sh.execut_db_sql(sql, dbname=self.db_name)
        self.log.info(select_res2)
        self.assertEqual(select_res1, select_res2, '执行失败:' + text)

        text = '-----step6:查询nlssort后的数据 expect:成功-----'
        self.log.info(text)
        sql = f"select nlssort(col_2,'nls_sort = schinese_pinyin_m') " \
            f"from {self.t_name};"
        select_res3 = self.pri_sh.execut_db_sql(sql, dbname=self.db_name)
        self.log.info(select_res3)
        select_exp3 = ['021B01F401EF01F40000020202020002020202',
                       '01EF023801F4000002020200060707',
                       '01F401F401EF000002020200070706',
                       '972955B500000101000101', 'A4654FBC00000101000101',
                       '6145B85C00000101000101', 'A4654FBC00000101000101',
                       '65640000010001', '3E45942E4861000001010100010101',
                       '942E486100000101000101']
        for i in range(10):
            self.assertIn(select_exp3[i], select_res3.splitlines()[-(11 - i)],
                          '执行失败:' + text)

        text = '-----step7:查询数据nlssort拼音排序; expect:成功-----'
        self.log.info(text)
        sql = f"select * from {self.t_name} order by " \
            f"nlssort(col_1,'nls_sort = schinese_pinyin_m') asc,col_2;"
        select_res4 = self.pri_sh.execut_db_sql(sql, dbname=self.db_name)
        self.log.info(select_res4)
        for i in range(10):
            self.assertIn(select_exp1[i], select_res4.splitlines()[-(11 - i)],
                          '执行失败:' + text)

        text = '-----step8:查询数据不同nlssort拼音排序; expect:成功,不等价上一条语句-----'
        self.log.info(text)
        sql = f"select * from {self.t_name} order by " \
            f"nlssort(1,'NLS_SORT = SCHINESE_PINYIN_M') asc,col_2;"
        select_res5 = self.pri_sh.execut_db_sql(sql, dbname=self.db_name)
        self.log.info(select_res5)
        self.assertNotEqual(select_res4, select_res5, '执行失败:' + text)

        text = '-----step9:创建list_list二级分区表 expect:成功-----'
        self.log.info(text)
        create_sql = f"drop table if exists {self.t_name};" \
            f"create table {self.t_name} (col_1 VARCHAR2 ( 30 ), " \
            f"col_2 VARCHAR2 ( 30 ) not null, col_3 varchar2 ( 30 ) , " \
            f"col_4 VARCHAR2 ( 30 ) )with(FILLFACTOR=80)" \
            f"partition by list (col_1) subpartition by list (col_2)" \
            f"(partition p_list_1 values('图书馆')" \
            f"(subpartition p_list_1_1 values ('被删除')," \
            f"subpartition p_list_1_2 values ( default ))," \
            f"partition p_list_2 values('银行')" \
            f"(subpartition p_list_2_1 values ('还有')," \
            f"subpartition p_list_2_2 values ( default )," \
            f"subpartition p_list_2_3 values ('删除')," \
            f"subpartition p_list_2_4 values ( 'ccb')," \
            f"subpartition p_list_2_5 values ('icbc'))," \
            f"partition p_list_3 values('农业银行')" \
            f"(subpartition p_list_3_2 values ( default ))," \
            f"partition p_list_4 values(default )," \
            f"partition p_list_5 values('知识银行')" \
            f"(subpartition p_list_5_1 values ( '剂')," \
            f"subpartition p_list_5_2 values ( default )," \
            f"subpartition p_list_5_3 values ('被删除')," \
            f"subpartition p_list_5_4 values ('删除')," \
            f"subpartition p_list_5_5 values ('icbc'))," \
            f"partition p_list_6 values('中国银行')," \
            f"partition p_list_7 values('招商银行')" \
            f"(subpartition p_list_7_1 values ( default ))" \
            f") enable row movement;"
        msg = self.pri_sh.execut_db_sql(create_sql, dbname=self.db_name)
        self.log.info(msg)
        self.assertIn(self.constant.DROP_TABLE_SUCCESS, msg, '执行失败:' + text)
        self.assertIn(self.constant.CREATE_TABLE_SUCCESS, msg, '执行失败:' + text)

        text = '-----step10:插入数据并查询分区数据nlssort拼音排序 expect:成功-----'
        self.log.info(text)
        sql = f"insert into {self.t_name} values('工商银行','icbc')," \
            f"('中国银行','BOC'),('建设银行','CCB'),('设银','是否')," \
            f"('银行','稳定');" \
            f"insert into {self.t_name} values('招商银行','还有')," \
            f"('农业银行','稳定'),('知识银行','剂'),('图书馆','被删除'),('图书馆','删除');" \
            f"select * from {self.t_name} subpartition" \
            f"(p_list_4_subpartdefault1) order by " \
            f"nlssort(col_1,'nls_sort = schinese_pinyin_m') asc,col_2;"
        msg = self.pri_sh.execut_db_sql(sql, dbname=self.db_name)
        self.log.info(msg)
        self.assertEqual(2, msg.count(self.constant.INSERT_SUCCESS_MSG),
                         '执行失败:' + text)
        self.assertIn('3 rows', msg, '执行失败:' + text)
        self.assertIn('工商银行', msg.splitlines()[-4], '执行失败:' + text)
        self.assertIn('建设银行', msg.splitlines()[-3], '执行失败:' + text)
        self.assertIn('设银', msg.splitlines()[-2], '执行失败:' + text)

        text = '-----step11:创建hash_hash二级分区表 expect:成功-----'
        self.log.info(text)
        create_sql = f"drop table if exists {self.t_name};" \
            f"create table {self.t_name} (col_1 VARCHAR2 ( 30 ), " \
            f"col_2 VARCHAR2 ( 30 ) not null, col_3 varchar2 ( 30 ) , " \
            f"col_4 VARCHAR2 ( 30 ) )with(FILLFACTOR=80)" \
            f"partition by hash (col_1) subpartition by hash (col_2)" \
            f"(partition p_range_1 " \
            f"(subpartition p_hash_1_1 ,subpartition p_hash_1_2 ," \
            f" subpartition p_hash_1_3,subpartition p_hash_1_4," \
            f" subpartition p_hash_1_5,subpartition p_hash_1_6)," \
            f"partition p_range_2," \
            f"partition p_range_3 " \
            f"(subpartition p_hash_3_1 ,subpartition p_hash_3_2 ," \
            f" subpartition p_hash_3_3)," \
            f"partition p_range_4" \
            f"(subpartition p_hash_4_1)," \
            f"partition p_range_5" \
            f") enable row movement;"
        msg = self.pri_sh.execut_db_sql(create_sql, dbname=self.db_name)
        self.log.info(msg)
        self.assertIn(self.constant.DROP_TABLE_SUCCESS, msg, '执行失败:' + text)
        self.assertIn(self.constant.CREATE_TABLE_SUCCESS, msg, '执行失败:' + text)

        text = '-----step12:插入数据并查询分区数据nlssort拼音排序 expect:成功-----'
        self.log.info(text)
        sql = f"insert into {self.t_name} values('工商银行','icbc')," \
            f"('中国银行','BOC'),('建设银行','CCB'),('设银','是否')," \
            f"('银行','稳定');" \
            f"insert into {self.t_name} values('招商银行','还有')," \
            f"('农业银行','稳定'),('知识银行','剂'),('图书馆','被删除'),('图书馆','删除');" \
            f"select * from {self.t_name} subpartition(p_hash_4_1) " \
            f"order by nlssort(col_1,'NLS_SORT = SCHINESE_PINYIN_M')," \
            f"nlssort(col_2,'NLS_SORT = SCHINESE_PINYIN_M');"
        msg = self.pri_sh.execut_db_sql(sql, dbname=self.db_name)
        self.log.info(msg)
        self.assertEqual(2, msg.count(self.constant.INSERT_SUCCESS_MSG),
                         '执行失败:' + text)
        self.assertIn('4 rows', msg, '执行失败:' + text)
        self.assertIn('农业银行 | 稳定', msg.splitlines()[-5], '执行失败:' + text)
        self.assertIn('图书馆   | 被删除', msg.splitlines()[-4], '执行失败:' + text)
        self.assertIn('图书馆   | 删除', msg.splitlines()[-3], '执行失败:' + text)
        self.assertIn('知识银行 | 剂', msg.splitlines()[-2], '执行失败:' + text)

        text = '-----step13:查询分区数据nlssort拼音排序 ' \
               'expect:成功,第一列相同的时候,col_2的排序与上一条相反-----'
        self.log.info(text)
        sql = f"select * from {self.t_name} subpartition(p_hash_4_1) " \
            f"order by nlssort(col_1,'NLS_SORT = SCHINESE_PINYIN_M')," \
            f"nlssort(col_2,'NLS_SORT = SCHINESE_PINYIN_M') desc;"
        msg = self.pri_sh.execut_db_sql(sql, dbname=self.db_name)
        self.log.info(msg)
        self.assertIn('4 rows', msg, '执行失败:' + text)
        self.assertIn('农业银行 | 稳定', msg.splitlines()[-5], '执行失败:' + text)
        self.assertIn('图书馆   | 删除', msg.splitlines()[-4], '执行失败:' + text)
        self.assertIn('图书馆   | 被删除', msg.splitlines()[-3], '执行失败:' + text)
        self.assertIn('知识银行 | 剂', msg.splitlines()[-2], '执行失败:' + text)

    def tearDown(self):
        self.log.info('-----step14:清理环境-----')
        text_1 = '-----删除表和库 expect:成功-----'
        self.log.info(text_1)
        del_cmd = f'drop table if exists {self.t_name};' \
            f'drop database if exists {self.db_name};'
        del_msg = self.pri_sh.execut_db_sql(del_cmd)
        self.log.info(del_msg)

        text_2 = '-----删除copy文件 expect:成功-----'
        self.log.info(text_2)
        rm_cmd = f'rm -rf {self.copy_file};cat {self.copy_file};'
        rm_msg = self.primaryNode.sh(rm_cmd).result()
        self.log.info(rm_msg)

        self.assertIn(self.constant.DROP_DATABASE_SUCCESS, del_msg,
                      '执行失败:' + text_1)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, del_msg,
                      '执行失败:' + text_1)
        self.assertIn(self.constant.NO_FILE_MSG, rm_msg, '执行失败:' + text_2)
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
